Difference Context Functions
As described in the context calculation overview, the Difference functions are designed to provide an easy technique to calculate the difference between data points in a result set. While these can be done in semantic formulas, they are much more appropriate as context logic, because they will change to suit the selections made in a report or visual - which is significantly more efficient.
Difference Logic
The core principal behind difference calculations is to compare data points in a query and easily determine the difference between the values as you move from one item to the next. Although this concept is extended to comparing to the first and last as well, the key element is that the logic looks at the list of items (from a hierarchy) and checks the movement of data from one "row" to the next on the selected metric. By changing the hierarchy or the selections, the differential will change accordingly, without needing to redefine or recode the calculation itself (i.e. it is contextual).
Examples
Example 1. Difference to Previous, Difference to First
Using the grid below:
- Difference to Previous: Apr 2016 to Mar 2016 represents an increase of 9837.97
- Difference to First: Aug 2016 to Jan 2016, represents an increase of 3483.32
Example 2. Percentage of Next, Percentage of Last
Using the grid below:
- Percentage of Next : Jan 2016 is 128.37% of the Feb 2016
- Percentage of Last: Jun 2016 is 123.08% of Dec 2016
Example 3. Percentage Difference of Previous
Using the grid below:
- Percentage Difference of Previous : The difference between Feb 2016 and Jan 2016, as a percent of Jan 2016 is -22.10% (-25,197.02 / 114,022.85)
Example 4. Using Difference Calcs to drive a Waterfall chart
One of the best use cases for the logic is to drive the Waterfall visualization.
The first step, involves plotting the measure on a chart with categories (usually time or something like accounts)
Next, add a difference calc based on cost, and use it to drive coloring for cost using the "positive / negative" option. In this way, bars that are going up compared to the prior month are green. Bars that are going down appear red.
In the last step, choose the waterfall visualization option.
And the chart will be transformed into a classic waterfall chart,
Editing Context Calculation Logic
As well as editing and changing the properties of a context calculation, such as its name or format, it is also possible to alter the logic of how the calculation is performed using the Context Calculation Logic Editor.